/*********************************************************************************
 *      Copyright:  (C) 2022 guoyi<675088383@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  sqlite_db.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(11/12/21)
 *         Author:  guoyi<675088383@qq.com>
 *      ChangeLog:  1, Release initial version on "22/2/26 13:51:54"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

#include "sqlite_db.h"
#include "zlog_api.h"

static int select_callback(void *data, int argc, char **argv, char **col_name);

#if 0
int main(int argc, char **argv)
{
    sqlite3        *db;
    char           *db_name = "client.db";
    char           *tb_name = "temperature";
    sample_data_t   data;
    strncpy(data.sn, "QLQ", sizeof(data.sn));
    strncpy(data.time, "2022-4-6", sizeof(data.time));
    data.temper = 11.111;
    db_init(&db, db_name, tb_name);
    db_push_record(db, tb_name, &data, sizeof(data));
    char         sql[256] = {0};
    snprintf(sql, sizeof(sql), "select * from %s;", tb_name);
    db_query_record(db, sql);
    return 0;
}
#endif

static int select_callback(void *data, int argc, char **argv, char **col_name)
{
    int i;
    log_debug("----------\n");
    for(i = 0; i < argc; i++)
    {
        log_debug("%s: %s\n", col_name[i], argv[i] ? argv[i] : "NULL");
    }
    log_debug("----------\n");
    return 0;
}
/*数据库打开.初始化*/
int db_init(sqlite3 **db, const char *db_name, const char *tb_name)
{
    int                 rv = 0;
    char               *errmsg = NULL;
    char                sql[SQL_COMMAND_LEN];

    if( !db_name || !tb_name )
    {
        log_warn("ERROR: Invalid input arguments\n");
        return -1;
    }
    if( SQLITE_OK != sqlite3_open(db_name, db) )
    {
        log_warn("ERROR: Open/create sqlite database '%s' failure: %s\n", db_name, sqlite3_errmsg(*db));
        return -2;
    }

    log_debug("Open/create sqlite database '%s' successfully!\n", db_name);

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS %s(\
        ID INTEGER PRIMARY KEY AUTOINCREMENT,\
        SN CHAR(16),\
        DATETIME CHAR(32),\
        TEMPER CHAR(16))", tb_name);

    if( SQLITE_OK != sqlite3_exec(*db, sql, NULL, NULL, &errmsg) )
    {
        log_warn("ERROR: Open/create table '%s' in datebase '%s' failure: %s\n", tb_name, db_name, errmsg);
        sqlite3_free(errmsg);
        rv = -3;
        goto OUT;
    }

    log_debug("Open/create table '%s' successfully!\n", tb_name);

OUT:
    if( rv < 0 )
    {
        log_warn("ERROR: Initialize database '%s' failure\n", db_name);
        sqlite3_close(*db);
        unlink(db_name);
    }

    log_debug("Initialize database '%s' successfully!\n", db_name);
    return rv;
}
/*获取记录*/
int db_count_record(sqlite3 *db, const char *tb_name)
{
    int                 rv = 0; 
    char                sql[SQL_COMMAND_LEN];
    char               *errmsg = NULL;
    char              **result = NULL;
    int                 rownum;
    int                 colnum;

    if( !tb_name )
    {
        log_warn("ERROR: Invalid input arguments\n");
        return -1;
    }

    if( !db )
    {
        log_warn("ERROR: SQLite database not initialise\n");
        return -2;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "select count(*) from %s;", tb_name);
    if( SQLITE_OK != sqlite3_get_table(db, sql, &result, &rownum, &colnum, &errmsg) )
    {
        log_warn("ERROR: Query records count from database failure: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -3;
    }
    if( rownum <= 0 )
    {
        log_warn("ERROR: Query records count from database failure: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -4;
    }

    rv = atoi(result[1]);
    return rv;
}
/*增加记录到数据库中*/
int db_push_record(sqlite3 *db, const char *tb_name, sample_data_t *data, int bytes)
{
    char               *errmsg = NULL;
    char                sql[SQL_COMMAND_LEN];
    char                temper_str[16] = {0};

    if( !tb_name || !data)
    {
        log_warn("ERROR: Invalid input arguments\n");
        return -1;
    }
    if( !db )
    {
        log_warn("ERROR: SQLite database not initialise\n");
        return -2;
    }

    snprintf(temper_str, sizeof(temper_str), "%.3f℃", data->temper);
    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "insert into %s(SN, DATETIME, TEMPER)\
            values('%s','%s','%s');", tb_name, data->sn, data->time, temper_str);

    if( SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        log_warn("ERROR: Push temperature record into sqlite database failure: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -3;
    }

    log_debug("Push temperature record into sqlite database okay\n");

    return 0;
}
/*获取数据*/
int db_get_record(sqlite3 *db, const char *tb_name, sample_data_t *data, int bytes)
{
    int                 row, col;
    int                 rv;
    int                 index;
    char              **pResult;
    char                sql[SQL_COMMAND_LEN];
    char                temper_str[16] = {0};
    if( !tb_name || !data )
    {
        log_warn("ERROR: Invalid input arguments\n");
        return -1;
    }
    if( !db )
    {
        log_warn("ERROR: SQLite database not initialise\n");
        return -2;
    }
    memset(sql, 0, sizeof(sql));
    snprintf((char *)sql, sizeof(sql), "select * from %s limit 1;", tb_name);
    rv = sqlite3_get_table(db, sql, &pResult, &row, &col, NULL);
    if( SQLITE_OK != rv )
    {
        log_warn("ERROR: Database operate sqlite3_get_table() failure\n");
        return -3;
    }
    index = col;
    index++;
    strncpy(data->sn, pResult[index++], sizeof(data->sn));
    strncpy(data->time, pResult[index++], sizeof(data->time));
    snprintf(temper_str, sizeof(temper_str), "%.3f", atof(pResult[index++]));
    data->temper = atof(temper_str);
    return 0;
}

/*删除数据*/
int db_del_1stRecord(sqlite3 *db, const char *tb_name)
{
    char                sql[128];
    char               *errmsg = NULL;

    if( !tb_name )
    {
        log_warn("ERROR: Invalid input arguments\n");
        return -1;
    }

    if( !db )
    {
        log_warn("ERROR: SQLite database not initialise\n");
        return -2;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "delete from %s limit 0,1;", tb_name);

    if( SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        log_warn("ERROR: Delete first record from table %s failure: %s\n", tb_name, errmsg);
        sqlite3_free(errmsg);
        return -3;
    }

    log_debug("Delete first record from table %s successfully!\n", tb_name);

    return 0;
}

int db_query_record(sqlite3 *db, char *sql)
{
    char       *errmsg = NULL;
    void       *data = NULL;

    if( SQLITE_OK != sqlite3_exec(db, sql, select_callback, data, &errmsg))
    {
        log_warn("Query temperature record failure: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    else
        log_debug("Query temperature record successfully!\n");

    return 0;
}
/*关闭数据库*/
void db_term(sqlite3 *db)
{
    if( db )
        sqlite3_close(db);

    log_debug("Close sqlite database successfully!\n");
}
